﻿using B.S.InOut.Infacation.Information;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using System.Threading.Tasks;
using System.Data;

namespace B.S.InOut.Infacation.Imagination
{
    public class BaseREpository<T> : IBaseRepository<T> where T : class
    {
        private readonly string _connectionString= "server=10.223.8.86;uid=sa;pwd=123456;database=UserBackend";
        

        public BaseREpository(string connectionString)
        {
            _connectionString = connectionString;
        }

        public SqlConnection CreateConnection()
        {
            return new SqlConnection(_connectionString);
        }

        public async Task<int> InsertAsync(T entity)
        {
            using var connection = CreateConnection();
            await connection.OpenAsync();
            // 需要根据实体动态生成SQL语句
            return await connection.ExecuteAsync("INSERT INTO ProductModel values", entity);
        }

        public async Task<bool> UpdateAsync(T entity)
        {
            using var connection = CreateConnection();
            await connection.OpenAsync();
            // 需要根据实体动态生成SQL语句
            return await connection.ExecuteAsync("UPDATE ProductModel set ", entity) > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = CreateConnection();
            await connection.OpenAsync();
            return await connection.ExecuteAsync("DELETE FROM ProductModel WHERE Id = @Id", new { Id = id }) > 0;
        }

        public async Task<T> GetByIdAsync(int id)
        {
            using var connection = CreateConnection();
            await connection.OpenAsync();
            return await connection.QueryFirstOrDefaultAsync<T>("SELECT * FROM ProductModel WHERE Id = @Id", new { Id = id });
        }

        public async Task<IEnumerable<T>> GetAllAsync()
        {
            using var connection = CreateConnection();
            await connection.OpenAsync();
            return await connection.QueryAsync<T>("SELECT * FROM ProductModel");
        }

        //#region
        ///// <summary>
        ///// 获取所有实体记录。
        ///// </summary>
        //public virtual async Task<IEnumerable<T>> GetAllAsync()
        //{
        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();
        //    return await connection.QueryAsync<T>("SELECT * FROM Product");
        //}

        ///// <summary>
        ///// 根据ID获取单个实体记录。
        ///// </summary>
        //public virtual async Task<T> GetByIdAsync(int id)
        //{
        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();
        //    return await connection.QuerySingleOrDefaultAsync<T>("SELECT * FROM Product WHERE Id = @Id", new { Id = id });
        //}

        ///// <summary>
        ///// 插入新的实体记录。
        ///// </summary>
        //public virtual async Task<int> InsertAsync(T entity)
        //{
        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();
        //    return await connection.ExecuteAsync("INSERT INTO Product (ProductName, ManufacturerCode,InternalCode,UnitId,Category,UpperLimit,LowerLimit,Specification,Price,WHouseId,WHouseTypeId,Description) VALUES (@ProductName, @ManufacturerCode,@InternalCode,@UnitId,@Category,@UpperLimit,@LowerLimit,@Specification,@Price,@WHouseId,@WHouseTypeId,@Description)", entity);
        //}

        ///// <summary>
        ///// 更新现有的实体记录。
        ///// </summary>
        //public virtual async Task UpdateAsync(T entity)
        //{
        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();
        //    await connection.ExecuteAsync("UPDATE Product SET ProductName = @ProductName, ManufacturerCode = @ManufacturerCode,InternalCode=@InternalCode,UnitId=@InternalCode,Category=@Category,UpperLimit=@UpperLimit,LowerLimit=@LowerLimit,Specification=@Specification,Price=@Price,WHouseId=@WHouseId,WHouseTypeId=@WHouseTypeId,Description=@Description WHERE Id = @Id", entity);
        //}

        ///// <summary>
        ///// 删除指定ID的实体记录。
        ///// </summary>
        //public virtual async Task DeleteAsync(int id)
        //{
        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();
        //    await connection.ExecuteAsync("DELETE FROM Product WHERE Id = @Id", new { Id = id });
        //}
        ///// <summary>
        ///// 执行分页查询并返回结果。
        ///// </summary>
        ///// <param name="sql">基础查询SQL语句。</param>
        ///// <param name="orderBy">排序字段和顺序（如"Id ASC"）。</param>
        ///// <param name="pageIndex">当前页码（从1开始）。</param>
        ///// <param name="pageSize">每页显示的记录数。</param>
        ///// <returns>包含分页数据的任务。</returns>
        //public async Task<IEnumerable<T>> GetPageListAsync(string sql, string orderBy, int pageIndex, int pageSize)
        //{
        //    if (pageIndex < 1) pageIndex = 1; // 确保页码至少为1

        //    var skip = (pageIndex - 1) * pageSize;
        //    var sb = new StringBuilder();

        //    // 构建分页SQL语句
        //    sb.AppendFormat("SELECT COUNT(1) FROM ({0}) AS Result;", sql); // 总计数子查询
        //    sb.AppendFormat(
        //        @"SELECT *
        //          FROM (
        //              SELECT *, ROW_NUMBER() OVER(ORDER BY {1}) AS RowNum
        //              FROM ({0}) AS Temp
        //          ) AS Result
        //          WHERE RowNum BETWEEN {2} AND {3}
        //          ORDER BY {1}",
        //        sql, orderBy, skip + 1, skip + pageSize);

        //    using var connection = CreateConnection();
        //    await connection.OpenAsync();

        //    // 执行查询并获取结果
        //    return await connection.QueryAsync<T>(sb.ToString());
        //}
        //#endregion

    }
}
